

<?php

/**
 * This class generates standard stored procedures for every table in your database
 * This means that at the end of execution you'll have 5 stored procedures for each table 
 * Those stored procedures are:
 * 
 * - Get All Fields and Records From Table.
 * - Get All Fields From Table By Primary Key.
 * - Insert Into Table.
 * - Update Table By Primary Key.
 * - Delete from Table By Primary Key.
 * 
 * 
 * @name storedProceduresAutoGenerator
 * @author Anas K. Al-Far <anas@al-far.com>
 * @copyright http://anas.al-far.com 
 * @copyright Anas K. Al-Far
 * @copyright The Stored Procedures Auto Generator Class has been released with source code under the GPL free software license.
 * @version 1.0.0
 * @access public
 *
 */

class storedProceduresAutoGenerator {
	/**
	 * Database Host or IP
	 *
	 * @var string
	 * @access private
	 */
	private $db_host 		= null;
	/**
	 * Database Username
	 *
	 * @var string
	 * @access private
	 */
	private $db_username 	= null;
	/**
	 * Database Password
	 *
	 * @var string
	 * @access private
	 */
	private $db_password 	= null;
	/**
	 * Database Name
	 *
	 * @var string 
	 * @access private
	 */
	private $db_name 		= null;
	/**
	 * Hold the DB Link
	 *
	 * @var string
	 * @access private
	 */
	private $db_link		= null;
	/**
	 * Enter description here...
	 *
	 * @var string
	 * @access private
	 */
	private $path	 		= null;
	/**
	 * Initialize needed variables
	 *
	 * @param string $host
	 * @param string $username
	 * @param string $password
	 * @param string $db
	 * @return  boolean
	 * @access public
	 */
	public function __construct($db_host, $db_username, $db_password, $db_name, $path){
		$this->db_host 		= $db_host;
		$this->db_username 	= $db_username;
		$this->db_password 	= $db_password;
		$this->db_name 		= $db_name;
		$this->path 		= $path;

		//check if the variables are not empty
		if (is_null($this->db_host)) {
			exit('Please provide a DB Host.');
		}

		if (is_null($this->db_username)) {
			exit('Please provide a DB Username.');
		}

		if (is_null($this->db_password)) {
			exit('Please provide a DB Password.');
		}

		if (is_null($this->db_name)) {
			exit('Please provide a DB Name.');
		}

		if (is_null($this->path)) {
			exit('Please provide a Physical Path.');
		}

		//check if path is a valid directory
		if (!is_dir($this->path)) {
			exit('Please provide a valid path for an existing and writable directory.');
		}

		//check if directory is a writable
		if (!is_writable($this->path)) {
			exit('Please provide a valid path for a writable directory.');
		}

		//connet to the DB
		if (($this->db_link = mysql_connect($db_host, $db_username, $db_password)) === false) {
			exit('Please provide a valid DB-Connection.');
		}

		//select the DB
		if (($dbSelected = mysql_select_db($db_name)) === false) {
			exit('Please provide a valid DB-Name.');
		}

		return true;
	}

	/**
	 * Enter description here...
	 *
	 * @return unknown
	 */
	public function __generateSP() {
		//initialize variable to fill in the stored procedures
		$storedProcedures = '';

		//get DB tables
		$query = mysql_query("SHOW TABLES ", $this->db_link);

		//fill in DB tables
		while ($row = mysql_fetch_array($query)) {
			$dbTables[] = $row[0];
		}

		//inialize counter to 0
		$j = 0;

		foreach ($dbTables as $key=>$value) {
			//initialize counter to 0
			$i = 0;

			//select all data from current table to fetch table columns
			$result = mysql_query("SELECT * FROM $value", $this->db_link);
			$meta = mysql_fetch_field($result, $i);

			while ($i < mysql_num_fields($result)) {
				$meta = mysql_fetch_field($result, $i);

				//fill the datatypes array
				$dataTypes["$meta->type"] 		= $meta->type;

				//fill in the table name
				$dbFields[$j][$i][]				= $value;

				if ($meta->primary_key){
					$dbFields[$j]['primary'][]= $meta->name;
					$dbFields[$j]['primary'][]= (0 === strcasecmp($meta->type, 'string') ? 'varchar' : $meta->type);
					$dbFields[$j]['primary'][]= (($meta->max_length == 0) ? '' : '('.$meta->max_length.')');
				}

				//fill the column information
				$dbFields[$j][$i][]		= $meta->name;
				$dbFields[$j][$i][]		= (0 === strcasecmp($meta->type, 'string') ? 'varchar' : $meta->type);
				$dbFields[$j][$i][]		= $meta->numeric;
				$dbFields[$j][$i][]		= $meta->not_null;
				$dbFields[$j][$i][]		= $meta->blob;
				$dbFields[$j][$i][]		= (($meta->max_length == 0) ? '' : '('.$meta->max_length.')');
				$dbFields[$j][$i][] 	= $meta->unique_key;
				$dbFields[$j][$i][]		= $meta->primary_key;
				$dbFields[$j][$i][]		= $meta->def;
				$dbFields[$j][$i][]		= $meta->multiple_key;
				$dbFields[$j][$i][] 	= $meta->unsigned;
				$dbFields[$j][$i][] 	= $meta->zerofill;

				$i++;
			}

			$j++;
		}
		mysql_free_result($result);
		mysql_close();

		$countTables = count($dbFields);
		$tablesMainMenu 	= '<div style="float:left;">';

		for ($i = 0; $i < $countTables; $i++){
			//stored procedures body text
			$getTables = '';
			$getTableDetails = '';
			$insertTable = '';
			$updateTable = '';
			$deleteTable = '';

			//stored procedures parameters
			$updateTableParam 	= '';
			$insertTableParam 	= '';
			$whereCondParam 	= '';
			$whereCondition 	= '';

			//stored procedures parameters list inside the sp
			$updateTableList 	= '';
			$insertTableList 	= '';
			$getTableDetailsList= '';
			$getTableList 		= '';
			$insertValuesList 	= '';

			$countFields 		= count($dbFields[$i]) -1 ;
			$tableName 			= $dbTables[$i];
			$tablePrimaryKey 	= $dbFields[$i]['primary'][0];
			$getTableList 		.= '`'.$tablePrimaryKey . '`, ';

			for ($j=0; $j<$countFields; $j++){
				if ($dbFields[$i][$j][8]){
					if (0 === strcasecmp($dbFields[$i][$j][2], 'varchar')) {
						$whereCondParam 	.= 'IN p' . ucfirst($tablePrimaryKey) . ' ' . $dbFields[$i][$j][2] . ' varchar (255) ' . $dbFields[$i][$j][6];
					} else {
						$whereCondParam 	.= 'IN p' . ucfirst($tablePrimaryKey) . ' ' . $dbFields[$i][$j][2] . ' ' . $dbFields[$i][$j][6];
					}
					$whereCondition 	.= '`'.$tablePrimaryKey . '` = ' . 'p' . ucfirst($tablePrimaryKey);
					$getTableDetailsList.= '`'.$tablePrimaryKey . '`, ';

					//parameters
					$updateTableParam 	.= "\nIN p" . ucfirst($tablePrimaryKey) . ' ' . $dbFields[$i][$j][2] . ' ' . $dbFields[$i][$j][6] . ',';
				} else {
					if (0 === strcasecmp($dbFields[$i][$j][2], 'varchar')) {
						//parameters
						$updateTableParam 	.= "\nIN p" . ucfirst($dbFields[$i][$j][1]) . ' ' . $dbFields[$i][$j][2] . ' varchar (255) ' . $dbFields[$i][$j][6] . ',';
						$insertTableParam 	.= "\nIN p" . ucfirst($dbFields[$i][$j][1]) . ' ' . $dbFields[$i][$j][2] . ' varchar (255) ' . $dbFields[$i][$j][6] . ',';
					} else {
						//parameters
						$updateTableParam 	.= "\nIN p" . ucfirst($dbFields[$i][$j][1]) . ' ' . $dbFields[$i][$j][2] . ' ' . $dbFields[$i][$j][6] . ',';
						$insertTableParam 	.= "\nIN p" . ucfirst($dbFields[$i][$j][1]) . ' ' . $dbFields[$i][$j][2] . ' ' . $dbFields[$i][$j][6] . ',';
					}

					//fields list in the query
					$updateTableList 	.= '`'.$dbFields[$i][$j][1] . '` = p' . ucfirst($dbFields[$i][$j][1]) . ', ';
					$insertTableList 	.= '`'.$dbFields[$i][$j][1] . '`, ';
					$getTableDetailsList.= '`'.$dbFields[$i][$j][1] . '`, ';
					$getTableList 		.= '`'.$dbFields[$i][$j][1] . '`, ';
					$insertValuesList 	.= 'p' . ucfirst($dbFields[$i][$j][1]) . ', ';
				}
			}

			$updateTableParam 	= substr($updateTableParam, 0, -1);
			$insertTableParam 	= substr($insertTableParam, 0, -1);
			$updateTableList 	= substr($updateTableList, 0, -2);
			$insertTableList 	= substr($insertTableList, 0, -2);
			$getTableDetailsList= substr($getTableDetailsList, 0, -2);
			$getTableList 		= substr($getTableList, 0, -2);
			$insertValuesList	= substr($insertValuesList, 0, -2);
			$__now = date('Y-m-d H:i:s');

			$deleteTable = "/************************************************/\r
/* Auto Generated Stored Procedure     		*/\r
/* 						*/\r
/* SP Name: SP_Delete".ucfirst($tableName)."ByID		*/\r
/* Generated Date:	$__now	*/\r
/* Author: Anas K. Al-Far			*/\r
/* Copyright: anas@maktoob.com			*/\r
/* Copyright: http://anas.al-far.com			*/\r
/* Copyright: This Stored Procedure has been released with under the GPL free software license.*/
/************************************************/\r
\r
DROP PROCEDURE IF EXISTS SP_Delete".ucfirst($tableName)."ByID;\r
\r
DELIMITER //\r
CREATE PROCEDURE SP_Delete".ucfirst($tableName)."ByID(\r
".$whereCondParam."\r
)\r
BEGIN\r
\r
DELETE \r
FROM 	`".$tableName."` \r
WHERE 	`".$tablePrimaryKey."` = p".ucfirst($tablePrimaryKey).";\r
\r
END //\r
DELIMITER ;\r";

			$insertTable = "/************************************************/\r
/* Auto Generated Stored Procedure     		*/\r
/* 						*/\r
/* SP Name: SP_InsertInto".ucfirst($tableName)."		*/\r
/* Generated Date:	$__now	*/\r
/* Author: Anas K. Al-Far			*/\r
/* Copyright: anas@maktoob.com			*/\r
/* Copyright: http://anas.al-far.com			*/\r
/* Copyright: This Stored Procedure has been released with under the GPL free software license.*/
/************************************************/\r
\r
DROP PROCEDURE IF EXISTS SP_InsertInto".ucfirst($tableName).";\r
\r
DELIMITER //\r
CREATE PROCEDURE SP_InsertInto".ucfirst($tableName)."(\r
".$insertTableParam."\r
)\r
BEGIN\r
\r
INSERT INTO `".$tableName."` (".$insertTableList.") \r
VALUES (".$insertValuesList.");\r
\r
SELECT MAX (`".$tablePrimaryKey."`) FROM `".$tableName."`;\r
\r
END //\r
DELIMITER ;\r";
			$getTables = "/************************************************/\r
/* Auto Generated Stored Procedure     		*/\r
/* 						*/\r
/* SP Name: SP_GetAll".ucfirst($tableName)."			*/\r
/* Generated Date:	$__now	*/\r
/* Author: Anas K. Al-Far			*/\r
/* Copyright: anas@maktoob.com			*/\r
/* Copyright: http://anas.al-far.com			*/\r
/* Copyright: This Stored Procedure has been released with under the GPL free software license.*/
/************************************************/\r
\r
DROP PROCEDURE IF EXISTS SP_GetAll".ucfirst($tableName).";\r
\r
DELIMITER //\r
CREATE PROCEDURE SP_GetAll".ucfirst($tableName)."(\r
\r
)\r
BEGIN\r
\r
SELECT 	".$getTableList."\r
FROM 	`".$tableName."`;\r
\r
END //\r
DELIMITER ;\r";
			$getTableDetails = "/************************************************/\r
/* Auto Generated Stored Procedure     		*/\r
/* 						*/\r
/* SP Name: SP_Get".ucfirst($tableName)."DetailsByID	*/\r
/* Generated Date:	$__now	*/\r
/* Author: Anas K. Al-Far			*/\r
/* Copyright: anas@maktoob.com			*/\r
/* Copyright: http://anas.al-far.com			*/\r
/* Copyright: This Stored Procedure has been released with under the GPL free software license.*/
/************************************************/\r
\r
DROP PROCEDURE IF EXISTS SP_Get".ucfirst($tableName)."DetailsByID;\r
\r
DELIMITER //\r
CREATE PROCEDURE SP_Get".ucfirst($tableName)."DetailsByID(\r
".$whereCondParam."\r
)\r
BEGIN\r
\r
SELECT 	".$getTableDetailsList."\r
FROM 	`".$tableName."` \r
WHERE 	".$whereCondition.";\r
\r
END //\r
DELIMITER ;\r";
			$updateTable = "/************************************************/\r
/* Auto Generated Stored Procedure     		*/\r
/* 						*/\r
/* SP Name: SP_Update".ucfirst($tableName)."ByID		*/\r
/* Generated Date:	$__now	*/\r
/* Author: Anas K. Al-Far			*/\r
/* Copyright: anas@maktoob.com			*/\r
/* Copyright: http://anas.al-far.com			*/\r
/* Copyright: This Stored Procedure has been released with under the GPL free software license.*/
/************************************************/\r
\r
DROP PROCEDURE IF EXISTS SP_Update".ucfirst($tableName)."ByID;\r
\r
DELIMITER //\r
\r
CREATE PROCEDURE SP_Update".ucfirst($tableName)."ByID(\r
".$updateTableParam."\r
)\r
BEGIN\r
\r
UPDATE 	`".$tableName."` \r
SET 	".$updateTableList."\r
WHERE 	".$whereCondition.";\r
\r
END //\r
DELIMITER ;\r";

			file_put_contents($this->path.DIRECTORY_SEPARATOR.'sql'.DIRECTORY_SEPARATOR.'Update'.ucfirst($tableName).'ByID.sql', $updateTable);
			chmod($this->path.DIRECTORY_SEPARATOR.'sql'.DIRECTORY_SEPARATOR.'Update'.ucfirst($tableName).'ByID.sql', 0755);
			file_put_contents($this->path.DIRECTORY_SEPARATOR.'sql'.DIRECTORY_SEPARATOR.'InsertInto'.ucfirst($tableName).'.sql', $insertTable);
			chmod($this->path.DIRECTORY_SEPARATOR.'sql'.DIRECTORY_SEPARATOR.'InsertInto'.ucfirst($tableName).'.sql', 0755);
			file_put_contents($this->path.DIRECTORY_SEPARATOR.'sql'.DIRECTORY_SEPARATOR.'GetAll'.ucfirst($tableName).'.sql', $getTables);
			chmod($this->path.DIRECTORY_SEPARATOR.'sql'.DIRECTORY_SEPARATOR.'GetAll'.ucfirst($tableName).'.sql', 0755);
			file_put_contents($this->path.DIRECTORY_SEPARATOR.'sql'.DIRECTORY_SEPARATOR.'Get'.ucfirst($tableName).'DetailsByID.sql', $getTableDetails);
			chmod($this->path.DIRECTORY_SEPARATOR.'sql'.DIRECTORY_SEPARATOR.'Get'.ucfirst($tableName).'DetailsByID.sql', 0755);
			file_put_contents($this->path.DIRECTORY_SEPARATOR.'sql'.DIRECTORY_SEPARATOR.'Delete'.ucfirst($tableName).'ByID.sql', $deleteTable);
			chmod($this->path.DIRECTORY_SEPARATOR.'sql'.DIRECTORY_SEPARATOR.'Delete'.ucfirst($tableName).'ByID.sql', 0755);
		}

		return true;
	}
}

$generateSP = new storedProceduresAutoGenerator('localhost', 'todouser', 'todopasswd', 'todo', '/tmp/test');
if ($generateSP->__generateSP()) {
	print 'All stored procedures has been created succesfully.';
} else {
	print '<br />Operation Not Completed.';
}
unset($generateSP);

?>